﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<得到当日的提醒信息 给缓存用>
-- =============================================
CREATE PROCEDURE [dbo].[proc_TimeRemind_RemindListForDay]
(
	@UserName VARCHAR(50)
)

AS
SET DATEFIRST 1 --设定每周的第一天为星期一
BEGIN

SELECT RemindTime AS RemindExt,*  FROM TimeRemind 
WHERE UserName=@UserName AND TimeType=1   -- 一次提醒
AND RemindTime>=DATEADD(Minute,-10,GETDATE()) 
AND RemindTime<=DATEADD(DAY,1,GETDATE()) 
AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Minute,-10,GETDATE()))

UNION

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10),GETDATE(),120)+' '+RemindDay) AS RemindExt,*  
FROM TimeRemind 
WHERE UserName=@UserName AND TimeType=2
AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Minute,-10,GETDATE()))

UNION

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10),GETDATE(),120)+' '+RemindDay) AS RemindExt,*  
FROM TimeRemind 
WHERE UserName=@UserName AND TimeType=3
AND charindex(','+ltrim(DATEPART(weekday,GETDATE()))+',',','+RemindWeekday+',')>0
AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Minute,-10,GETDATE()))

UNION

SELECT GETDATE() AS RemindExt,*  FROM TimeRemind -- 每月提醒
WHERE UserName=@UserName AND TimeType=4 
AND ( charindex(','+ltrim(DAY(GETDATE()))+',',','+RemindMonth+',')>0 --判断当前日期是否需要提醒
	OR charindex(','+ltrim(DAY(GETDATE())+1)+',',','+RemindMonth+',')>0  --提前一天
	OR charindex(','+ltrim(DAY(GETDATE())+2)+',',','+RemindMonth+',')>0
	OR charindex(','+ltrim(DAY(GETDATE())+3)+',',','+RemindMonth+',')>0
)
AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Hour,-12,GETDATE())) --提醒的间隔时间为12小时

UNION

SELECT GETDATE() AS RemindExt,* FROM TimeRemind --每年提醒
WHERE UserName=@UserName AND TimeType=5
AND CONVERT(DATETIME,Convert(varchar(4),year(GETDATE()))+'-'+RemindYear)>=DATEADD(DAY,-1,GETDATE()) -- 时间范围 -1当天
AND CONVERT(DATETIME,Convert(varchar(4),year(GETDATE()))+'-'+RemindYear)<=DATEADD(DAY,3,GETDATE()) -- 
AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Hour,-12,GETDATE())) --提醒的间隔时间为12小时

------------------------
--更新已经被提醒过的记录 每月 每年
------------------------
UPDATE TimeRemind
SET LastRemindTime = GETDATE()
WHERE UserName=@UserName AND TimeType=4 AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Hour,-12,GETDATE())) --提醒的间隔时间为12小时
AND ( charindex(','+ltrim(DAY(GETDATE()))+',',','+RemindMonth+',')>0
	OR charindex(','+ltrim(DAY(GETDATE())+1)+',',','+RemindMonth+',')>0
	OR charindex(','+ltrim(DAY(GETDATE())+2)+',',','+RemindMonth+',')>0
	OR charindex(','+ltrim(DAY(GETDATE())+3)+',',','+RemindMonth+',')>0
)

UPDATE TimeRemind
SET LastRemindTime = GETDATE()
WHERE UserName=@UserName AND TimeType=5 AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Hour,-12,GETDATE())) --提醒的间隔时间为12小时
AND CONVERT(DATETIME,Convert(varchar(4),year(GETDATE()))+'-'+RemindYear)>=DATEADD(DAY,-1,GETDATE()) --时间范围 
AND CONVERT(DATETIME,Convert(varchar(4),year(GETDATE()))+'-'+RemindYear)<=DATEADD(DAY,3,GETDATE())


	SET NOCOUNT ON;

END